from tools.dbo import get_server_data_car, put_mysql_data, get_server_data, carried_sql, get_server_data_one, \
    get_dict_data_sql


# 获取样本源附加字段倒排矩阵
def get_sample_resource_type_metrix(sql_server_conn):
    sql = "select sampleSourceTypeId,customFieldId,fieldNo from AP_SP_SampleSourceTypeExAttribute where isDel = 0"
    return_data = get_server_data_car(sql_server_conn, sql)
    res = {}
    for item in return_data:
        tmp = {}
        if item[0] in res.keys():
            tmp = res.get(item[0])
        tmp[item[2]] = item[1]
        res[item[0]] = tmp
    return res

# 样本源自定义字段迁移
def syn_sample_resource_ex_value(sql_server_conn, mysql_conn):
    print("===============================正在迁移样本源扩展字段==============================")
    carried_sql(mysql_conn,"TRUNCATE sample_resource_ex_value")
    sample_resource_type_metrix = get_sample_resource_type_metrix(sql_server_conn)
    select_sample_resource_value_sql = "select * from AP_SP_SampleSourceTypeExData"
    cus_value_dict = get_dict_data_sql(sql_server_conn, select_sample_resource_value_sql)
    sample_resource_ex_value_list = []
    for item in cus_value_dict:
        typeId = item["sampleSourceTypeId"]
        if typeId not in sample_resource_type_metrix.keys():
            continue
        fields = sample_resource_type_metrix[typeId]
        for k, v in fields.items():
            if item[k] is None or item[k] == "":
                continue
            sample_resource_ex_value_list.append([item["sampleSourceId"],v,item[k],2])
    print(len(sample_resource_ex_value_list))
    print(put_mysql_data(mysql_conn, "sample_resource_id,cus_id,cus_value,tenant_id", "sample_resource_ex_value", sample_resource_ex_value_list))
    print("===============================样本源扩展字段迁移完成==============================")

# 样本源迁移
# SELECT id,sampleSourceTypeId,sampleSourceNo,sampleSourceNm,sampleSourceDesc,projectId,departId,isEffect,isDel from AP_SP_SampleSource
# SELECT id,type_id,code,name,remark,project_id,dept_id,is_delete from sample_resource;
def syn_sample_resource(sql_server_conn, mysql_conn):
    print("===============================正在迁移样本源==============================")
    carried_sql(mysql_conn,"TRUNCATE sample_resource")
    return_data = get_server_data(sql_server_conn, "id,sampleSourceTypeId,sampleSourceNo,sampleSourceNm,sampleSourceDesc,projectId,departId+99,isDel,isrtEmpId,isrtDt,updtEmpId,updtDt,2","1 = 1","AP_SP_SampleSource")
    print(put_mysql_data(mysql_conn, "id,type_id,code,name,remark,project_id,dept_id,is_delete,create_id,create_time,update_id,update_time,tenant_id", "sample_resource", return_data))
    print("==============================样本源迁移完成==============================")



# 样本源扩展属性迁移
def syn_sample_resource_type_attr(sql_server_conn, mysql_conn):
    print("===============================正在迁移样本源扩展属性==============================")
    carried_sql(mysql_conn,"TRUNCATE sample_resource_type_configuration_ex")
    return_data = get_server_data(sql_server_conn, "2,id,sampleSourceTypeId,customFieldId,groupNm,required,isDel,isrtEmpId,isrtDt,updtEmpId,updtDt","1 = 1","AP_SP_SampleSourceTypeExAttribute")
    print(put_mysql_data(mysql_conn, "tenant_id,id,sample_resource_type_configuration_id,cus_id,group_name,is_requried,is_delete,create_id,create_time,update_id,update_time", "sample_resource_type_configuration_ex", return_data))
    print("==============================样本源扩展属性迁移完成==============================")



# 样本源类型迁移
def syn_sample_resource_type(sql_server_conn, mysql_conn):
    print("===============================正在迁移样本源类型数据==============================")
    carried_sql(mysql_conn,"TRUNCATE sample_resource_type_configuration")
    return_data = get_server_data(sql_server_conn, "2,id,sampleSourceTypeNo,sampleSourceTypeNm,isEffect,isrtEmpId,isrtDt,updtEmpId,updtDt","isDel = 0","AP_SP_SampleSourceType")
    print(put_mysql_data(mysql_conn, "tenant_id,id,code,name,status,create_id,create_time,update_id,update_time", "sample_resource_type_configuration", return_data))
    print("===============================样本源类型数据迁移完成==============================")

#修改样本源拓展字段，以及修改样本拓展字段字典项 code->id-value
def sys_sample_ex_and_resource_ex_dict(sql_server_conn, mysql_conn):
    print("===============================正在修改样本源和样本的字典属性==============================")
    update_sample_info_ex_sql = """
    update sample_info_ex_value sie inner join 
    (
        select sie.id,sie.cus_value,concat(dict.id,'-',dict.label) new_cus_value from `sample_info_ex_value` sie
        left join bims_dict dict on sie.cus_value = dict.tmp
        where cus_id in(select id from cus_field where type_id=8) 
    )res on sie.id = res.id 
    set sie.cus_value = res.new_cus_value
    """
    update_sample_resource_ex_sql = """
    update sample_resource_ex_value sre inner join 
    (
        select sre.id,sre.cus_value,concat(dict.id,'-',dict.label) new_cus_value from `sample_resource_ex_value` sre
        left join bims_dict dict on sre.cus_value = dict.tmp
        where cus_id in(select id from cus_field where type_id=8) 
    )res on sre.id = res.id 
    set sre.cus_value = res.new_cus_value
    """
    carried_sql(mysql_conn, update_sample_info_ex_sql)
    carried_sql(mysql_conn, update_sample_resource_ex_sql)
    print("===============================样本源和样本的字典属性修改完成==============================")
